﻿using System;
using Database;
using System.Data;

namespace ObjectManager
{
    class SinhVienDAO : IDatabase
    {
        private ConnectionDB cnn = null;

        public SinhVienDTO SinhVienDTO { get; set; }

        public SinhVienDAO()
        {
            cnn = Connection.Connect();
            SinhVienDTO = new SinhVienDTO();
        }

        private bool GioiTinh()
        {
            bool GT = true;
            if (SinhVienDTO.GioiTinh == "Nam")
            {
                GT = true;
            }
            else
            {
                GT = false;
            }
            return GT;
        }

        private int MaLop()
        {
            int ML = 0;
            DataTable temp = cnn.QueryProcedure("TimMaLop", SinhVienDTO.Lop);
            if (temp != null && temp.Rows.Count > 0 && !string.IsNullOrEmpty(temp.Rows[0][0].ToString()))
            {
                ML = (int)temp.Rows[0][0];
            }
            return ML;
        }

        private int MaSV(string KhoaHoc)
        {
            DataTable temp = cnn.QueryProcedure("MaSinhVienLonNhat", KhoaHoc);
            int MaSV = 0;
            if (temp != null && temp.Rows.Count > 0 && !string.IsNullOrEmpty(temp.Rows[0][0].ToString()))
            {
                MaSV = (int)temp.Rows[0][0];
            }
            MaSV++;
            return MaSV;
        }

        public int Insert()
        {
            if (!string.IsNullOrEmpty(SinhVienDTO.Ten) && !string.IsNullOrEmpty(SinhVienDTO.Lop))
            {
                string KhoaHoc = SinhVienDTO.Lop.Substring(0, 2);
                int Ma = MaSV(KhoaHoc);
                SinhVienDTO.Ma = Ma.ToString() + KhoaHoc;
                string QueQuan = "";
                if (!string.IsNullOrEmpty(SinhVienDTO.QueQuan))
                {
                    QueQuan = SinhVienDTO.QueQuan;
                }
                return cnn.ExecuteProcedure("InsertSinhVien", Ma, KhoaHoc, SinhVienDTO.Ten, MaLop(), SinhVienDTO.NgaySinh, GioiTinh(), QueQuan);
            }
            return -1;
        }

        public int Update()
        {
            if (!string.IsNullOrEmpty(SinhVienDTO.Ma))
            {
                string Ma = SinhVienDTO.Ma.Substring(0, SinhVienDTO.Ma.Length - 2);
                string MaK = SinhVienDTO.Ma.Substring(SinhVienDTO.Ma.Length - 2);
                return cnn.ExecuteProcedure("UpdateSinhVien", Ma, MaK, SinhVienDTO.Ten, MaLop(), SinhVienDTO.NgaySinh, GioiTinh(), SinhVienDTO.QueQuan);
            }
            return -1;
        }

        public int Delete()
        {
            if (!string.IsNullOrEmpty(SinhVienDTO.Ma))
            {
                string Ma = SinhVienDTO.Ma.Substring(0, SinhVienDTO.Ma.Length - 2);
                string MaK = SinhVienDTO.Ma.Substring(SinhVienDTO.Ma.Length - 2);
                return cnn.ExecuteProcedure("DeleteSinhVien", Ma, MaK);
            }
            return -1;
        }

        public bool Search()
        {
            if (!string.IsNullOrEmpty(SinhVienDTO.Ma))
            {
                DataTable temp = cnn.Query("select * from vw_SinhVien where MaSV like {0};", SinhVienDTO.Ma);
                if (temp != null && temp.Rows.Count > 0)
                {
                    SinhVienDTO.Ten = (string)temp.Rows[0]["TenSV"];
                    SinhVienDTO.Lop = (string)temp.Rows[0]["TenLop"];
                    SinhVienDTO.BoMon = (string)temp.Rows[0]["TenBoMon"];
                    string date = (string)temp.Rows[0]["NgaySinh"];
                    string[] date_temp = date.Split('-');
                    DateTime datetime = new DateTime(int.Parse(date_temp[2]), int.Parse(date_temp[1]), int.Parse(date_temp[0]));
                    SinhVienDTO.NgaySinh = datetime;
                    SinhVienDTO.GioiTinh = (string)temp.Rows[0]["GioiTinh"];
                    SinhVienDTO.QueQuan = (string)temp.Rows[0]["QueQuan"];
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        private DataTable Search(string MaSinhVien)
        {
            return cnn.QueryProcedure("DanhSachSinhVien_Ma", MaSinhVien);
        }

        private DataTable Search(string TenSinhVien, string TenLop, string TenBoMon, string GioiTinh, string QueQuan)
        {
            if (string.IsNullOrEmpty(TenSinhVien))
            {
                TenSinhVien = "%";
            }
            else
            {
                TenSinhVien = "%" + TenSinhVien + "%";
            }
            if (string.IsNullOrEmpty(TenBoMon))
            {
                TenBoMon = "%";
            }
            if (string.IsNullOrEmpty(TenLop))
            {
                TenLop = "%";
            }
            if (string.IsNullOrEmpty(GioiTinh))
            {
                GioiTinh = "%";
            }
            if (string.IsNullOrEmpty(QueQuan))
            {
                QueQuan = "%";
            }
            else
            {
                QueQuan = "%" + QueQuan + "%";
            }
            return cnn.QueryProcedure("DanhSachSinhVien_TimKien", TenSinhVien, TenLop, TenBoMon, GioiTinh, QueQuan);
        }

        public DataTable Search(bool ma)
        {
            if (ma)
            {
                return Search(SinhVienDTO.Ma);
            }
            else
            {
                return Search(SinhVienDTO.Ten, SinhVienDTO.Lop, SinhVienDTO.BoMon, SinhVienDTO.GioiTinh, SinhVienDTO.QueQuan);
            }
        }

        public DataTable List()
        {
            return cnn.Query("select * from vw_SinhVien");
        }

        public string MaSinhVien(string lop)
        {
            if (string.IsNullOrEmpty(lop))
            {
                return "";
            }
            string KhoaHoc = lop.Substring(0, 2);
            int ma = MaSV(KhoaHoc);
            if (ma == 0)
            {
                return "";
            }
            return ma + KhoaHoc;
        }
    }
}
